package defunct;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.swing.JOptionPane;


// TODO come up with a cohesive way of searching for bands or returning results
// TODO implement search method for returning results

public class MySQL {

	public static void main(String[] args) {
	    // Create a connection to the database
	    String serverName = "localhost";
	    String mydatabase = "Tabasaurus";
	    String url = "jdbc:mysql://" + serverName +  "/" + mydatabase; // a JDBC url
	    String username = "nonsense";

	    Connection conn = establishSQLConnection(serverName, mydatabase, url, username, "");
	    
	    String bn = JOptionPane.showInputDialog(null, "Enter band name:", "create table", 1);
	    
//	    String statement = "CREATE TABLE " + bn + " (Band_Name char(25), Song_Name char(50), Song_URL char(128), Song_Rating char(8), Song_Rated char(6), Tab_Type char(10))";
	
//	    String statement = "INSERT " + bn + " VALUES ('Nothing Else Matters', 'http://www.fakewebaddress.com/totally/cool/metallica.html', 'r_22', '2357', 'TAB')";

	    if( MySQL.checkForBand(conn, bn) )	{
	    	System.out.println("Band: " + bn + " table exists!");
	    }
	    else	{
	    	System.out.println("Band: " + bn + " table DOES NOT EXIST!");
	    	System.out.println("Creating new table for: " + bn);
	    	MySQL.createNewBand(conn, bn);
	    }
	    
	    
	    MySQL.insertTab(conn, bn, "Nadda Foo!", "http://www.secureshit.com/", "r22", "324234", "tab");
	    
	}

	public static boolean checkForBand(Connection conn, String bandName)	{
		
		String statement = "SELECT * FROM `BandIndex` " +
						   " WHERE band_name LIKE '%" + bandName + "%'";
		
		try	{
			PreparedStatement ps = conn.prepareStatement(statement);
			ResultSet rs = ps.executeQuery();

			if( rs.first() )	{
				ps.close();
				return true;
			}
			else	{
				ps.close();
			}
		} catch (SQLException e) {
			System.out.println("Error SELECT [table]: " + e.getMessage());
//			e.printStackTrace();
		}
		
		return false;
	}
	
	public static boolean insertTab(Connection conn, String bandName, String songName, String songURL, String songRating, String songRated, String tabType)	{
		String statement = "INSERT INTO `" + bandName + "`("
	        + "Song_Name,"
	        + "Song_URL,"
	        + "Song_Rating,"
	        + "Song_Rated,"
	        + "Tab_Type)"
	        + "VALUES(?,?,?,?,?)";
		
		try {
			PreparedStatement ps = conn.prepareStatement(statement);
			ps.setString(1, songName);
			ps.setString(2, songURL);
			ps.setString(3, songRating);
			ps.setString(4, songRated);
			ps.setString(5, tabType);
			ps.executeUpdate();
			ps.close();
			return true;
		} catch (SQLException e) {
			System.out.println("Error INSERT: " + e.getMessage());
//			e.printStackTrace();
		}
		
		
		return false;
	}
	
	public static boolean createNewBand(Connection conn, String bandName)	{
	    String statement = "CREATE TABLE `" + bandName + "` (Song_Name char(50), Song_URL char(128), Song_Rating char(8), Song_Rated char(6), Tab_Type char(10))";
		
	    try {
			PreparedStatement ps = conn.prepareStatement(statement);
			ps.executeUpdate();
			ps.close();
			
			statement = "INSERT INTO `BandIndex`(" + 
						"band_name)" + 
						"VALUES(?)";
			
			ps = conn.prepareStatement(statement);
			ps.setString(1, bandName);
			
			ps.executeUpdate();
			ps.close();
			
			return true;
		} catch (SQLException e) {
			System.out.println("Error CREATE TABLE: " + e.getMessage());
//			e.printStackTrace();
		}

		return false;
	}
	
	public static Connection establishSQLConnection(String serverName, String database, String url, String user, String pass)	{
		Connection connection = null;
		
		try {
		    // Load the JDBC driver
		    String driverName = "org.gjt.mm.mysql.Driver"; // MySQL MM JDBC driver
		    Class.forName(driverName);

		    // Create a connection to the database
		    String password = JOptionPane.showInputDialog(null, "Enter password:", "mySQL Auth Required", 1);
		    connection = DriverManager.getConnection(url, user, password);
		} catch (ClassNotFoundException e) {
		    // Could not find the database driver
			e.printStackTrace();
		} catch (SQLException e) {
		    // Could not connect to the database
			System.out.println("SQL Error: " + e.getMessage());
//			e.printStackTrace();
		}
		return connection;
	}
	
	
	// TODO TEST THESE TWO FUNCTIONS! (UNTESTED!!)
/*
	
	public static long writeJavaObject(Connection conn, Object object) throws Exception {
		String className = object.getClass().getName();
		PreparedStatement pstmt = conn.prepareStatement(WRITE_OBJECT_SQL);
		
			    // set input parameters
		pstmt.setString(1, className);
		pstmt.setObject(2, object);
		pstmt.executeUpdate();
		
		// get the generated key for the id
		ResultSet rs = pstmt.getGeneratedKeys();
		int id = -1;
		if (rs.next()) {
			id = rs.getInt(1);
		}
		
		rs.close();
		pstmt.close();
		System.out.println("writeJavaObject: done serializing: " + className);
		return id;
	}
	
	public static Object readJavaObject(Connection conn, long id) throws Exception {
		PreparedStatement pstmt = conn.prepareStatement(READ_OBJECT_SQL);
		pstmt.setLong(1, id);
		ResultSet rs = pstmt.executeQuery();
		rs.next();
		Object object = rs.getObject(1);
		String className = object.getClass().getName();
		
		rs.close();
		pstmt.close();
		System.out.println("readJavaObject: done de-serializing: " + className);

		return object;
	}
*/
	
	
	
}
